package com.dashboard.taichinh;



import java.util.ArrayList;

import com.lb.jwitter.connectDatabase.ConnectBKPay;
import com.opensymphony.xwork2.Action;

public class ThongKeThuAction {

	private String jsonData;
	public String getJsonData() {
		return jsonData;
	}


	public void setJsonData(String jsonData) {
		this.jsonData = jsonData;
	}
	////////////////////////////////////////////////////////
	private String jsonData2;
	public String getJsonData2() {
		return jsonData2;
	}


	public void setJsonData2(String jsonData2) {
		this.jsonData2 = jsonData2;
	}
	////////////////////////////////////////////////////////
	private String namHoc;

	public String getNamHoc() {
		return namHoc;
	}


	public void setNamHoc(String namHoc) {
		this.namHoc = namHoc;
	}
	/////////////////////////////////////////////////////////
	private String hocKy;

	public String getHocKy() {
		return hocKy;
	}


	public void setHocKy(String hocKy) {
		this.hocKy = hocKy;
	}
	/////////////////////////////////////////////////////////
	

	public String execute() throws Exception
	{
		ConnectBKPay connection= new ConnectBKPay();
		if(connection.getConnection()!=null){
			//namHoc="14";
			//hocKy="1";
			
			//if(namHoc==null||namHoc.length()<1){
			//	return Action.ERROR;
			//}
			if(hocKy==null||hocKy.length()<1){
				return Action.ERROR;
			}
			
			//hocKy : 20141
			namHoc = hocKy.substring(2, 4); //endindex-1
			hocKy = hocKy.substring(4);
			ArrayList<String> listSemester=new ArrayList<String>();
			int year=Integer.parseInt(namHoc);
			//Lay ra 4 hoc ky lien tiep bao gom hoc ky hien tai va 3 hoc ky truoc do
			year=year-1;
			if(hocKy.equals("1")){
				String semester=year+"1";
				listSemester.add(semester);
				
				semester=year+"2";
				listSemester.add(semester);
				
				semester=year+"3";
				listSemester.add(semester);
				
				semester=namHoc+hocKy;
				listSemester.add(semester);
			}
			if(hocKy.equals("2")){
				
				String semester=year+"2";
				listSemester.add(semester);
				
				semester=year+"3";
				listSemester.add(semester);
				
				semester=namHoc+"1";
				listSemester.add(semester);
				
				semester=namHoc+hocKy;
				listSemester.add(semester);
			}
			if(hocKy.equals("3")){
				String semester=year+"3";
				listSemester.add(semester);
				
				semester=namHoc+"1";
				listSemester.add(semester);
				
				semester=namHoc+"2";
				listSemester.add(semester);
				
				semester=namHoc+hocKy;
				listSemester.add(semester);
			}
			
			//cau query tinh toan nguon thu, thuc thu, so tien da thu, so tien con lai, so nguoi da nop, so nguoi chua nop trong 4 hoc ky lien tiep
			String query="select d.nhhk_thangnam, d.loai_thanh_toan_id, sum( m.so_tien_can_thu_chi) as canthu,"
					+" sum(m.so_tien_tam_thu ) as tamthu, sum(k.thuc_thu) as thucthu,sum( k.con_lai) as conlai,"
					+" count( distinct m.cb_sv_id) as tongnguoi,"
					+" count(distinct case when k.con_lai > 0 then m.cb_sv_id end ) as sosvchuadong"
					+" from muc_thanh_toan m ,"
					+" (select muc_thanh_toan_id, sum(so_tien_thuc_thu_chi) as thuc_thu, sum(so_tien_con_lai) as con_lai"
					+" from khoan_thanh_toan"
					+" where thu_chi = 'IN' and da_huy != 1	group by muc_thanh_toan_id) as k ,"
					+" (select  dot_thanh_toan_id, loai_thanh_toan_id, nhhk_thangnam from dot_thanh_toan where"
					+" (nhhk_thangnam='"+listSemester.get(0)+"' or nhhk_thangnam='"+listSemester.get(1)+"'"
					+" or nhhk_thangnam='"+listSemester.get(2)+"' or nhhk_thangnam='"+listSemester.get(3)+"'"
					+" ) and loai_thanh_toan_id in"
					+" (select distinct loai_thanh_toan_id from loai_thanh_toan where thu_chi='IN') ) as d"
					+" where m.da_huy <>1"
					+" and m.muc_thanh_toan_id=k.muc_thanh_toan_id"
					+" and m.dot_thanh_toan_id =d.dot_thanh_toan_id"
					+" group by d.nhhk_thangnam,d.loai_thanh_toan_id";
			
			//nhom tinh nguon htu,thuc thu,...nop tre vao 1 cau query duy nhat
			/*
			 * 
			 * 
			 * String query="select d.nhhk_thangnam, d.loai_thanh_toan_id, sum( m.so_tien_can_thu_chi) as canthu,sum(k.nopTre)  as nopTre ,"
					+" sum(m.so_tien_tam_thu ) as tamthu, sum(k.thuc_thu) as thucthu,sum( k.con_lai) as conlai,"
					+" count( distinct m.cb_sv_id) as tongnguoi,"
					+" count(distinct case when k.con_lai > 0 then m.cb_sv_id end ) as sosvchuadong"
					+" from muc_thanh_toan m ,"
					+" (select muc_thanh_toan_id, sum(so_tien_thuc_thu_chi) as thuc_thu, sum(so_tien_con_lai) as con_lai,count(distinct case when ngay_thuc_thu_chi > ngay_du_kien_het_han then khoan_thanh_toan_id end) as nopTre "
					+" from khoan_thanh_toan"
					+" where thu_chi = 'IN' and da_huy != 1	group by muc_thanh_toan_id) as k ,"
					+" (select  dot_thanh_toan_id, loai_thanh_toan_id, nhhk_thangnam from dot_thanh_toan where"
					+" (nhhk_thangnam='"+listSemester.get(0)+"' or nhhk_thangnam='"+listSemester.get(1)+"'"
					+" or nhhk_thangnam='"+listSemester.get(2)+"' or nhhk_thangnam='"+listSemester.get(3)+"'"
					+" ) and loai_thanh_toan_id in"
					+" (select distinct loai_thanh_toan_id from loai_thanh_toan where thu_chi='IN') ) as d"
					+" where m.da_huy <>1"
					+" and m.muc_thanh_toan_id=k.muc_thanh_toan_id"
					+" and m.dot_thanh_toan_id =d.dot_thanh_toan_id"
					+" group by d.nhhk_thangnam,d.loai_thanh_toan_id";
			
			 * 
			 */
			String semester=namHoc+hocKy;
			//du lieu cua hoc ky hien tai dang va 4 hoc ky lien tiep
			//{"d1":hockyHienTai,"d2":4Hockylientiep}
			////[{"kind":"QT","canThu":10000,"tamThu":9000,"thucThu":8000,"conLai":1000,"tongNguoi":10","soNguoiDaNop":7,"soNguoiChuaNop":3,"soNguoiNopTre":5}]
			jsonData=connection.getJSonDataThongKe(query,semester);
			/*
			//du lieu cua hoc ky hien tai dang json
			//[{"kind":"QT","canThu":10000,"tamThu":9000,"thucThu":8000,"conLai":1000,"tongNguoi":10","soNguoiDaNop":7,"soNguoiChuaNop":3,"soNguoiNopTre":5}]
			jsonData=connection.getJSonDataThongKe(query,semester);
			
			//du lieu cua 4 hoc ky lien tiep tai dang json
			//[{"kind":"QT","canThu":10000,"tamThu":9000,"thucThu":8000,"conLai":1000,"tongNguoi":10","soNguoiDaNop":7,"soNguoiChuaNop":3,"soNguoiNopTre":5}]
			//jsonData2=connection.getJSonData2();
			*/
		}
		connection.closeConnection();
		return Action.SUCCESS;

	}

	
	
}
